﻿using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Linq.Mapping;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Text.RegularExpressions;
using System.Web;

namespace MyCommon
{
    /// <summary>
    /// 导出Excel通用类 --郭旭东
    /// </summary>
    /// <typeparam name="T">与Excel列建立映射关系的实体的类型</typeparam>
    public class ExcelDataHelper<T> where T : class, new()
    {
        /// <summary>
        /// Excel模板路径
        /// </summary>
        protected virtual string ExcelTemplatPath { get; private set; }
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="excelTemplatPath">Excel模板的路径</param>
        public ExcelDataHelper(string excelTemplatPath)
        {
            if (!File.Exists(excelTemplatPath))
            {
                throw new FileNotFoundException("Excel模板路径无效", excelTemplatPath);
            }
            if (Path.GetExtension(excelTemplatPath).ToLower() != ".xls")
            {
                throw new ArgumentException("传入路径不是Excel格式文件", "excelTemplatPath");
            }
            ExcelTemplatPath = excelTemplatPath;
        }
        /// <summary>
        /// 将DataTable数据导出到Response
        /// </summary>
        /// <param name="dt">数据</param>
        /// <param name="response">要导出到的Response</param>
        /// <param name="exportFileName">用户下载时的文件名称</param>
        public void ExportToExcel(DataTable dt, HttpResponse response, string exportFileName)
        {
            WriteToResponse(ExportToExcel(dt), response, exportFileName);
        }
        /// <summary>
        /// 将List数据导出到Response
        /// </summary>
        /// <param name="list">数据</param>
        /// <param name="response">要导出到的Response</param>
        /// <param name="exportFileName">用户下载时的文件名称</param>
        public void ExportToExcel(List<T> list, HttpResponse response, string exportFileName)
        {
            WriteToResponse(ExportToExcel(list), response, exportFileName);
        }

        void WriteToResponse(byte[] fileContent, HttpResponse response, string exportFileName)
        {
            response.ClearContent();
            response.ContentType = "application/vnd.ms-excel";
            response.AppendHeader("Pragma", "No-Cache");
            response.AppendHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", exportFileName));
            response.BinaryWrite(fileContent);
            response.End();
        }

        /// <summary>
        /// 将DataTable类型的数据导出到Excel，会将DataTable转化到实体集合，再导到Excel
        /// </summary>
        /// <param name="dt">DataTable类型的数据</param>
        /// <returns>Excel文件的字节数据</returns>
        public byte[] ExportToExcel(DataTable dt)
        {
            var list = dt.ConvertToList<T>();
            return ExportToExcel2(list);
        }

        /// <summary>
        /// 将实体集合导出到Excel，采用API操作Excel，实体类类定义需装饰ExcelSheet，属性需装饰ExcelColumn，以与Excel列建立映射关系
        /// </summary>
        /// <param name="list">要导出的数据</param>
        /// <returns>Excel文件的字节数据</returns>
        public byte[] ExportToExcel2(List<T> list)
        {
            byte[] fileContent;
            var sheetName = GetSheetName(false);
            var tempExcelFilePath = CreateNewFileFromTemplate();

            var properties = typeof(T).GetProperties().Where(m => Regex.IsMatch(ConvertColumnName(GetColumnName(m), ColumnNameType.ForAPI), @"^[A-Z]{1}$"));

            Workbook workbook = null;
            try
            {
                workbook = new Workbook(tempExcelFilePath, new LoadOptions(LoadFormat.Excel97To2003));
                var sheet = workbook.Worksheets[sheetName];
                var rowIndex = sheet.Cells.MaxDataRow + 1;
                foreach (var item in list)
                {
                    rowIndex++;
                    sheet.Cells.InsertRow(rowIndex);
                    foreach (var prop in properties)
                    {
                        var attr = GetExcelColumnAttribute(prop);
                        var value = prop.GetValue(item, null);
                        if (!string.IsNullOrEmpty(attr.FormatString))
                        {
                            value = string.Format(string.Format("{{0:{0}}}", attr.FormatString), value);
                        }
                        var cellName = ConvertColumnName(attr.Name, ColumnNameType.ForAPI) + rowIndex;
                        sheet.Cells[cellName].PutValue(value);
                    }
                }
            }
            finally
            {
                if (workbook != null)
                {
                    workbook.Save(tempExcelFilePath, SaveFormat.Excel97To2003);
                }
                fileContent = File.ReadAllBytes(tempExcelFilePath);
                File.Delete(tempExcelFilePath);
            }
            return fileContent;
        }

        /// <summary>
        /// 将实体集合导出到Excel，使用Sql语句操作Excel，实体类类定义需装饰ExcelSheet，属性需装饰ExcelColumn，以与Excel列建立映射关系
        /// </summary>
        /// <param name="list">要导出的数据</param>
        /// <returns>Excel文件的字节数据</returns>
        public byte[] ExportToExcel(List<T> list)
        {
            var sheetName = GetSheetName(true);
            var tempExcelFilePath = CreateNewFileFromTemplate();
            using (var conn = new OleDbConnection(GenerateConnectionString(tempExcelFilePath)))
            {
                conn.Open();
                var dtDbSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new[] { null, null, sheetName });
                var rows = dtDbSchema.Rows.Cast<DataRow>();
                if (rows.Any())
                {
                    var columnNames = rows.Select(m => (string)m["COLUMN_NAME"]);
                    var properties = typeof(T).GetProperties().Where(m => columnNames.Contains(GetColumnName(m)));
                    foreach (var item in list)
                    {
                        var tempList = new List<FieldNameAndParameterForDataInserting>();
                        foreach (var prop in properties)
                        {
                            var attr = GetExcelColumnAttribute(prop);
                            var value = prop.GetValue(item, null);
                            if (!string.IsNullOrEmpty(attr.FormatString))
                            {
                                value = string.Format(string.Format("{{0:{0}}}", attr.FormatString), value);
                            }
                            tempList.Add(new FieldNameAndParameterForDataInserting(attr.Name, "?", value));
                        }
                        using (var cmd = conn.CreateCommand())
                        {
                            cmd.CommandText = GenerateCommandString(sheetName, tempList);
                            cmd.Parameters.AddRange(tempList.Select(m => m.Param).ToArray());
                            var count = cmd.ExecuteNonQuery();
                        }
                    }

                }
            }
            var fileContent = File.ReadAllBytes(tempExcelFilePath);
            File.Delete(tempExcelFilePath);
            return fileContent;
        }
        /// <summary>
        /// 映射Excel列名，A、B、C……或F1、F2、F3……
        /// </summary>
        /// <param name="name"></param>
        /// <param name="nameType"></param>
        /// <returns></returns>
        string ConvertColumnName(string name, ColumnNameType nameType)
        {
            var result = "";
            var AlphaArray = System.Linq.Enumerable.Range(65, 26).Select(m => ((char)m).ToString()).ToList();
            var FNumArray = System.Linq.Enumerable.Range(1, 26).Select(m => string.Format("F{0}", m)).ToList();

            var index = AlphaArray.IndexOf(name);
            if (index < 0)
            {
                index = FNumArray.IndexOf(name);
            }
            if (index < 0)
            {
                result = name;
            }
            else
            {
                switch (nameType)
                {
                    case ColumnNameType.ForSql:
                        result = FNumArray[index];
                        break;
                    case ColumnNameType.ForAPI:
                        result = AlphaArray[index];
                        break;
                }
            }
            return result;
        }

        /// <summary>
        /// 复制出一个临时Excel文件
        /// </summary>
        /// <returns>新文件的路径</returns>
        string CreateNewFileFromTemplate()
        {
            var tempExcelFilePath = Path.Combine(Path.GetDirectoryName(ExcelTemplatPath), Path.ChangeExtension(Path.GetRandomFileName(), "xls"));
            File.Copy(ExcelTemplatPath, tempExcelFilePath, true);
            //如果模板是只读的，复制出的文件也是只读的，所以此处将只读去掉
            var fileAttributes = File.GetAttributes(tempExcelFilePath);
            File.SetAttributes(tempExcelFilePath, ~FileAttributes.ReadOnly & fileAttributes);
            return tempExcelFilePath;
        }
        /// <summary>
        /// 生成连接字符串
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <returns></returns>
        string GenerateConnectionString(string excelFilePath)
        {
            //Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\VsProjects\参考代码\Test\Test\Test.xls;Persist Security Info=False;Extended Properties="Excel 8.0;HDR=No;IMEX=2"
            //HDR表示要把第一行作为数据还是作为列名,作为数据用HDR=no,作为列名用HDR=yes;通过Imex表示只读写控制，0只读、1只写、2读写。
            var builder = new OleDbConnectionStringBuilder
            {
                Provider = "Microsoft.Jet.OLEDB.4.0",
                DataSource = excelFilePath,
            };
            builder["Extended Properties"] = "Excel 8.0;HDR=No;IMEX=2";
            return builder.ConnectionString;
        }
        /// <summary>
        /// 生成插入命令字符串
        /// </summary>
        /// <param name="sheetName"></param>
        /// <param name="list"></param>
        /// <returns></returns>
        string GenerateCommandString(string sheetName, List<FieldNameAndParameterForDataInserting> list)
        {
            var fieldNames = list.Select(m => string.Format("[{0}]", m.FieldName));
            var paramNames = System.Linq.Enumerable.Repeat<string>("?", list.Count);
            return string.Format("insert into [{0}] ({1}) values({2})", sheetName, string.Join(",", fieldNames.ToArray()), string.Join(",", paramNames.ToArray()));
        }
        /// <summary>
        /// 通过ExcelSheetAttribute获取数据类对应的Excel表格名
        /// </summary>
        /// <returns></returns>
        string GetSheetName(bool includeDollarSign)
        {
            var type = typeof(T);
            var attr = Attribute.GetCustomAttribute(type, typeof(ExcelSheetAttribute)) as ExcelSheetAttribute;
            var sheetName = attr == null ? type.Name : attr.Name;
            if (includeDollarSign)
            {
                if (!sheetName.EndsWith("$"))
                {
                    sheetName = sheetName + "$";
                }
            }
            else
            {
                if (sheetName.EndsWith("$"))
                {
                    sheetName = sheetName.Remove(sheetName.Length - 1);
                }
            }
            return sheetName;
        }
        /// <summary>
        /// 获取数据类的某属性对应的Excel列名，Excel列名一般为“F1、F2等”
        /// </summary>
        /// <param name="prop"></param>
        /// <returns></returns>
        ExcelColumnAttribute GetExcelColumnAttribute(PropertyInfo prop)
        {
            var attr = Attribute.GetCustomAttribute(prop, typeof(ExcelColumnAttribute)) as ExcelColumnAttribute;
            if (attr == null)
            {
                attr = new ExcelColumnAttribute { Name = prop.Name };
            }
            return attr;
        }

        string GetColumnName(PropertyInfo prop)
        {
            var attr = GetExcelColumnAttribute(prop);
            return attr.Name;
        }

        class FieldNameAndParameterForDataInserting
        {
            public FieldNameAndParameterForDataInserting(string fieldName, string paramName, object value)
            {
                FieldName = fieldName;
                Param = new OleDbParameter(paramName, value ?? "");
            }
            public string FieldName { get; private set; }
            public OleDbParameter Param { get; private set; }
        }
    }
    /// <summary>
    /// 标识映射为Excel表的类
    /// </summary>
    [AttributeUsage(AttributeTargets.Class, AllowMultiple = false, Inherited = false)]
    public class ExcelSheetAttribute : Attribute
    {
        /// <summary>
        /// 表名
        /// </summary>
        public string Name { get; set; }
    }

    /// <summary>
    /// 标识映射为Excel列的属性或字段
    /// </summary>
    [AttributeUsage(AttributeTargets.Property | AttributeTargets.Field, AllowMultiple = false)]
    public class ExcelColumnAttribute : DataAttribute
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public ExcelColumnAttribute()
            : base()
        {
            FormatString = "";
        }
        /// <summary>
        /// 数据的格式字符串
        /// </summary>
        public string FormatString { get; set; }
    }
    enum ColumnNameType
    {
        ForSql,
        ForAPI
    }
}
